package cn.itcast.spring.demo1;

import static org.hamcrest.CoreMatchers.nullValue;
import static org.junit.Assert.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import cn.itcast.spring.pojo.Account;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(value="classpath:applicationContext.xml")
public class JdbcOperate {

	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	
	
	/**
	 * jdbc模板添加操作
	 * @throws Exception
	 */
	@Test
	public void insertData() throws Exception {
		jdbcTemplate.update("insert into account values(?,?,?)", null,"王五",10000d);
	}
	
	
	
	/**
	 * 修改操作
	 */
	@Test
	public void updateData() throws Exception {
		jdbcTemplate.update("update account set name=? where id = ?", "赵六",4);
	}
	
	/**
	 * 删除操作
	 */
	@Test
	public void deleteData() throws Exception {
		jdbcTemplate.update("delete from account where id = ?", 4);
	}
	
	
	/**
	 * 查询一：查询用户的名字
	 * 参数一：查询的sql语句
	 * 参数二：返回值的class类型
	 * 参数三：sql语句所需要带上的参数
	 */
	@Test
	public void queryForname() throws Exception {
		String queryForObject = jdbcTemplate.queryForObject("select name from account where id = ?", String.class, 2);
		System.out.println(queryForObject);
	}
	/**
	 * 查询二：返回一个account对象
	 */
	@Test
	public void queryForAccount() throws Exception {
		Account queryForObject = jdbcTemplate.queryForObject("select * from account where id  = ? ", new MyRowMapper(), 2);
		System.out.println(queryForObject.getName());
	}
	/**
	 * 自定义我们自己的rowMapper实现RowMapper接口即可
	 * @author admin
	 *
	 */
	class MyRowMapper implements RowMapper<Account>{
		@Override
		public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
			Account account = new Account();
			//从结果集中遍历获取我们的id的值，类似于我们之前学过的jdbc的操作
			account.setId(rs.getInt("id"));
			account.setName(rs.getString("name"));
			account.setMoney(rs.getDouble("money"));
			return account;
		}
	}
	
	/**
	 * 查询集合操作:不带任何的查询条件
	 * @throws Exception
	 */
	@Test
	public void queryList() throws Exception {
		List<Account> query = jdbcTemplate.query("select * from account", new MyRowMapper());
		for (Account account : query) {
			System.out.println(account.getName());
		}
	}
	
	/**
	 * 查询集合的操作：带查询条件
	 * @throws Exception
	 */
	@Test
	public void queryListWithCond() throws Exception {
		List<Account> query = jdbcTemplate.query("select * from account where id = ?", new MyRowMapper(), 2);
		for (Account account : query) {
			System.out.println(account.getName());
		}
	}
	
	

}
